import datetime

from sqlalchemy import Table, Column, ForeignKey
from sqlalchemy.orm import relation, backref, mapper, deferred
from sqlalchemy.types import BigInteger, Integer, String, DateTime, Text, Float, Numeric, SmallInteger
from sqlalchemy.dialects.mysql import ENUM

from fidoweb.model.meta import Base

"""
Rules
	1. Tables and Inner relations are sorted in alphabet order, and Inner relations are defined after Tables
	2. About relations:
		For m-m and o-o relations: 'relation' defined in the first element of the relation
		For o-m(m-o) relations: 'relation' defined in the element of 'm' side
"""

"""
Tables
	CardSeller
	Discount
	School
	SchoolMap
	SchoolMapDefault
	SchoolMapGoogle
	SchoolMapLoc
	SchoolMapLocPic
	SchoolMapLocType
	SchoolMapLocTopic
	SchoolMapLocTopicPost
	SchoolMapPiece
	SchoolType
	Store
"""

"""
Inner relations
	CardSeller(mapLoc) o-o SchoolMapLoc(cardSeller)
	Discount(store) m-o Store(discounts)
	School(type) m-o SchoolType(schools)
	School(maps) o-m SchoolMap(school)
	SchoolMap(defaultMap) o-o SchoolMapDefault(schoolMap)
	SchoolMap(googleMap) o-o SchoolMapGoogle(schoolMap)
	SchoolMapDefault(mapPieces) o-m SchoolMapPiece(map)
	SchoolMapDefault(mapLocs) m-m SchoolMapLoc(defaultMaps)
	SchoolMapGoogle(mapLocs) m-m SchoolMapLoc(googleMaps)
	SchoolMapLoc(pics) o-m SchoolMapLocPic(mapLoc)
	SchoolMapLoc(types) m-m SchoolMapLocType(mapLocs)
	SchoolMapLoc(topics) o-m SchoolMapLocTopic(mapLoc)
	SchoolMapLoc(store) o-o Store(mapLoc)
	SchoolMapLocTopic(posts) o-m SchoolMapLocTopicPost(topic)
"""

"""
Outer relations
	CardApplication(seller) m-o CardSeller(applications)
	City(schools) o-m School(city)
	Discount(users) m-m User(discounts) (Describing how much a user like the discount)
	School(users) m-m User(schools) (Describing what schools a user has been to, including the profession)
	SchoolMapLoc(users) m-m User(schoolMapLocs) (Describing how much a user like the location)
	SchoolMapLocTopic(user) m-o User(schoolMapLocTopics)
	SchoolMapLocTopicPost(user) m-o User(schoolMapLocTopicPosts)
"""

CardSeller_t = Table('cardSellers', Base.metadata,
	Column('id', BigInteger, ForeignKey('schoolMapLocs.id'), primary_key = True),
	Column('description', Text)
)

Discount_t = Table('discounts', Base.metadata,
	Column('id', BigInteger, primary_key = True),
	Column('name', String(200)),
	Column('createTime', DateTime(timezone = True), index = True),
	Column('validTime', DateTime(timezone = True), index = True),
	Column('expireTime', DateTime(timezone = True), index = True),
	Column('largePic', String(256)),
	Column('smallPic', String(256)),
	Column('store_id', BigInteger, ForeignKey('stores.id'))
)

School_t = Table('schools', Base.metadata,
	Column('id', Integer, primary_key = True),
	Column('name', String(64), unique = True),
	Column('type_id', Integer, ForeignKey('schoolTypes.id')),
	Column('city_id', Integer, ForeignKey('cities.id'))
)

SchoolMap_t = Table('schoolMaps', Base.metadata,
	Column('id', Integer, primary_key = True),
	Column('name', String(128), unique = True),
	Column('school_id', Integer, ForeignKey('schools.id')),
)

SchoolMapDefault_t = Table('schoolMapsDefault', Base.metadata,
	Column('id', Integer, ForeignKey('schoolMaps.id'), primary_key = True),
	Column('width', Integer),
	Column('height', Integer)
)

SchoolMapGoogle_t = Table('schoolMapsGoogle', Base.metadata, 
	Column('id', Integer, ForeignKey('schoolMaps.id'), primary_key = True),
	Column('zoom', Integer),
	Column('posX', Numeric(precision = 16, scale = 12)),
	Column('posY', Numeric(precision = 16, scale = 12))
)

SchoolMapLoc_t = Table('schoolMapLocs', Base.metadata,
	Column('id', BigInteger, primary_key = True),
	Column('name', String(64), index = True),
	Column('address', String(256), index = True),
	Column('updateTime', DateTime(timezone = True), index = True),
	Column('visitCount', BigInteger, index = True),
	Column('avgRating', Float, index = True),
	Column('topicCount', BigInteger, index = True),
	Column('ranking', Float, index = True),
	Column('description', Text),
)

SchoolMapLocPic_t = Table('schoolMapLocPics', Base.metadata,
	Column('id', BigInteger, primary_key = True),
	Column('schoolMapLoc_id', BigInteger, ForeignKey('schoolMapLocs.id')),
	Column('picCode', String(16), unique = True),
	Column('createTime', DateTime(timezone = True), index = True),
	Column('largePic', String(256)),
	Column('smallPic', String(256))
)

SchoolMapLocType_t = Table('schoolMapLocTypes', Base.metadata,
	Column('id', Integer, primary_key = True),
	Column('name', String(64), unique = True)
)

SchoolMapLocTopic_t = Table('schoolMapLocTopics', Base.metadata,
	Column('id', BigInteger, primary_key = True),
	Column('user_id', BigInteger, ForeignKey('users.id')),
	Column('schoolMapLoc_id', BigInteger, ForeignKey('schoolMapLocs.id')),
	Column('createTime', DateTime(timezone = True), index = True),
	Column('updateTime', DateTime(timezone = True), index = True),
	Column('postCount', Integer, index = True),
	Column('title', Text),
	Column('content', Text),
)

SchoolMapLocTopicPost_t = Table('schoolMapLocTopicPosts', Base.metadata,
	Column('id', BigInteger, primary_key = True),
	Column('user_id', BigInteger, ForeignKey('users.id')),
	Column('createTime', DateTime(timezone = True), index = True),
	Column('updateTime', DateTime(timezone = True), index = True),
	Column('content', Text),
	Column('topic_id', BigInteger, ForeignKey('schoolMapLocTopics.id')),
)

SchoolMapPiece_t = Table('schoolMapPieces', Base.metadata,
	Column('id', BigInteger, primary_key = True),
	Column('posX', Integer, index = True),
	Column('posY', Integer, index = True),
	Column('width', Integer),
	Column('height', Integer),
	Column('filename', String(256)),
	Column('schoolMap_id', Integer, ForeignKey('schoolMapsDefault.id'))
)

SchoolType_t = Table('schoolTypes', Base.metadata, 
	Column('id', Integer, primary_key = True), 
	Column('name', String(64), unique = True)
)

Store_t = Table('stores', Base.metadata,
	Column('id', BigInteger, ForeignKey('schoolMapLocs.id'), primary_key = True)
)

SchoolMapLoc_SchoolMapLocType_t = Table('schoolMapLocs_schoolMapLocTypes', Base.metadata,
	Column('schoolMapLoc_id', BigInteger, ForeignKey('schoolMapLocs.id'), primary_key = True),
	Column('schoolMapLocType_id', Integer, ForeignKey('schoolMapLocTypes.id'), primary_key = True)
)

SchoolMapDefault_SchoolMapLoc_t = Table('schoolMapsDefault_schoolMapLocs', Base.metadata, 
	Column('schoolMapDefualt_id', Integer, ForeignKey('schoolMapsDefault.id'), primary_key = True),
	Column('schoolMapLoc_id', BigInteger, ForeignKey('schoolMapLocs.id'), primary_key = True),
	Column('posX', Integer),
	Column('posY', Integer)
)

SchoolMapGoogle_SchoolMapLoc_t = Table('schoolMapsGoogle_schoolMapLocs', Base.metadata, 
	Column('schoolMapGoogle_id', Integer, ForeignKey('schoolMapsGoogle.id'), primary_key = True),
	Column('schoolMapLoc_id', BigInteger, ForeignKey('schoolMapLocs.id'), primary_key = True),
	Column('posX', Numeric(precision = 16, scale = 12)),
	Column('posY', Numeric(precision = 16, scale = 12))
)

Discount_User_t = Table('discounts_users', Base.metadata,
	Column('discount_id', Integer, ForeignKey('discounts.id'), primary_key = True),
	Column('user_id', BigInteger, ForeignKey('users.id'), primary_key = True),
	Column('type', ENUM('like', 'dislike'), index = True),
	Column('createTime', DateTime(timezone = True), index = True),
)

School_User_t = Table('schools_users', Base.metadata, 	
	Column('school_id', Integer, ForeignKey('schools.id'), primary_key = True),
	Column('user_id', BigInteger, ForeignKey('users.id'), primary_key = True),
	Column('profession_id', Integer, ForeignKey('professions.id')),
	Column('grade', Integer, index = True)
)

SchoolMapLoc_User_t = Table('schoolMapLocs_users', Base.metadata,
	Column('schoolMapLoc_id', BigInteger, ForeignKey('schoolMapLocs.id'), primary_key = True),
	Column('user_id', BigInteger, ForeignKey('users.id'), primary_key = True),
	Column('rating', SmallInteger, index = True),
	Column('visitCount', Integer, index = True)
)

class CardSeller(object) :
	def __init__(self, schoolMapLoc_id) :
		self.id = schoolMapLoc_id

class Discount(object) :
	def __init__(self, name, validTime, expireTime, largePic, smallPic, store_id) :
		self.name = name
		self.createTime = datetime.datetime.now()
		self.validTime = validTime
		self.expireTime = expireTime
		self.largePic = largePic
		self.smallPic = smallPic
		self.store_id = store_id

class School(object) :
	def __init__(self, name, type_id, city_id) :
		self.name = name
		self.type_id = type_id
		self.city_id = city_id

class SchoolMap(object) :
	def __init__(self, name, school_id) :
		self.name = name
		self.school_id = school_id
		
class SchoolMapDefault(object) :
	def __init__(self, schoolMap_id, width, height) :
		self.id = schoolMap_id
		self.width = width
		self.height = height

class SchoolMapGoogle(object) :
	def __init__(self, schoolMap_id, zoom, posX, posY) :
		self.id = schoolMap_id
		self.zoom = zoom
		self.posX = posX
		self.posY = posY

class SchoolMapLoc(object) :
	def __init__(self, name, address, description) :
		self.name = name
		self.address = address
		self.updateTime = datetime.datetime.now()
		self.avgRating = 0.0
		self.visitCount = 0
		self.topicCount = 0
		self.ranking = 0.0
		self.description = description

class SchoolMapLocTopic(object) :
	def __init__(self, user_id, schoolMapLoc_id, title, content) :
		self.user_id = user_id
		self.schoolMapLoc_id = schoolMapLoc_id
		self.createTime = datetime.datetime.now()
		self.updateTime = datetime.datetime.now()
		self.title = title
		self.content = content
		self.postCount = 0

class SchoolMapLocTopicPost(object) :
	def __init__(self, user_id, content, topic_id) :
		self.user_id = user_id
		self.createTime = datetime.datetime.now()
		self.updateTime = datetime.datetime.now()
		self.content = content
		self.topic_id = topic_id

class SchoolMapLocPic(object) :
	def __init__(self, schoolMapLoc_id, smallPic, largePic, picCode) :
		self.schoolMapLoc_id = schoolMapLoc_id
		self.smallPic = smallPic
		self.largePic = largePic
		self.createTime = datetime.datetime.now()
		self.picCode = picCode

class SchoolMapLocType(object) :
	def __init__(self, name) :
		self.name = name
			
class SchoolMapPiece(object) :
	def __init__(self, posX, posY, width, height, filename, schoolMap_id) :
		self.posX = posX
		self.posY = posY
		self.width = width
		self.height = height
		self.filename = filename
		self.schoolMap_id = schoolMap_id

class SchoolType(object) :
	def __init__(self, name) :
		self.name = name

class Store(object) :
	def __init__(self, id) :
		self.id = id

class SchoolMapDefault_SchoolMapLoc(object) :
	def __init__(self, schoolMapDefault_id, schoolMapLoc_id, posX, posY) :
		self.schoolMapDefault_id = schoolMapDefault_id
		self.schoolMapLoc_id = schoolMapLoc_id
		self.posX = posX
		self.posY = posY

class SchoolMapGoogle_SchoolMapLoc(object) :
	def __init__(self, schoolMapGoogle_id, schoolMapLoc_id, posX, posY) :
		self.schoolMapGoogle_id = schoolMapGoogle_id
		self.schoolMapLoc_id = schoolMapLoc_id
		self.posX = posX
		self.posY = posY

class SchoolMapLoc_SchoolMapLocType(object) :
	def __init__(self, schoolMapLoc_id, schoolMapLocType_id) :
		self.schoolMapLoc_id = schoolMapLoc_id
		self.schoolMapLocType_id = schoolMapLocType_id

class Discount_User(object) :
	def __init__(self, discount_id, user_id, like) :
		self.discount_id = discount_id
		self.user_id = user_id
		self.like = like

class School_User(object) :
	def __init__(self, school_id, user_id, profession_id, grade) :
		self.school_id = school_id
		self.user_id = user_id
		self.profession_id = profession_id
		self.grade = grade

class SchoolMapLoc_User(object) :
	def __init__(self, schoolMapLoc_id, user_id, rating) :
		self.schoolMapLoc_id = schoolMapLoc_id
		self.user_id = user_id
		self.rating = rating
		self.visitCount = 0

from fidoweb.model.user import User, CardApplication
mapper(CardSeller, CardSeller_t, properties = {
	'id' : CardSeller_t.c.id,
	'mapLoc' : relation(SchoolMapLoc, backref = 'cardSeller'),
	'applications' : relation(CardApplication, backref = 'seller')
})

mapper(Discount, Discount_t, properties = {
	'id' : Discount_t.c.id,
	'name' : Discount_t.c.name,
	'createTime' : Discount_t.c.createTime,
	'validTime' : Discount_t.c.validTime,
	'expireTime' : Discount_t.c.expireTime,
	'largePic' : Discount_t.c.largePic,
	'smallPic' : Discount_t.c.smallPic,
	'store_id' : Discount_t.c.store_id,
	'users' : relation(User, secondary = Discount_User_t, backref = 'discounts'),
})

mapper(School, School_t, properties = {
	'id' : School_t.c.id,
	'name' : School_t.c.name,
	'type_id' : School_t.c.type_id,
	'city_id' : School_t.c.city_id,
	'maps' : relation(SchoolMap, backref = 'school'),
	'users' : relation(User, secondary = School_User_t, backref = 'schools'),
})

mapper(SchoolMap, SchoolMap_t, properties = {
	'id' : SchoolMap_t.c.id,
	'name' : SchoolMap_t.c.name,
	'school_id' : SchoolMap_t.c.school_id,
	'defaultMap' : relation(SchoolMapDefault, backref = 'schoolMap'),
	'googleMap' : relation(SchoolMapGoogle, backref = 'schoolMap')
})

mapper(SchoolMapDefault, SchoolMapDefault_t, properties = {
	'id' : SchoolMapDefault_t.c.id,
	'width' : SchoolMapDefault_t.c.width,
	'height' : SchoolMapDefault_t.c.height,
	'mapPieces' : relation(SchoolMapPiece, backref = 'map'),
	'mapLocs' : relation(SchoolMapLoc, secondary = SchoolMapDefault_SchoolMapLoc_t, backref = 'defaultMaps', order_by = SchoolMapLoc_t.c.ranking.desc())
})

mapper(SchoolMapGoogle, SchoolMapGoogle_t, properties = {
	'id' : SchoolMapGoogle_t.c.id,
	'zoom' : SchoolMapGoogle_t.c.zoom,
	'posX' : SchoolMapGoogle_t.c.posX,
	'posY' : SchoolMapGoogle_t.c.posY,
	'mapLocs' : relation(SchoolMapLoc, secondary = SchoolMapGoogle_SchoolMapLoc_t, backref = 'googleMaps', order_by = SchoolMapLoc_t.c.ranking.desc())
})

mapper(SchoolMapLoc, SchoolMapLoc_t, properties = {
	'id' : SchoolMapLoc_t.c.id,
	'address' : SchoolMapLoc_t.c.address,
	'name' : SchoolMapLoc_t.c.name,
	'updateTime' : SchoolMapLoc_t.c.updateTime,
	'visitCount' : SchoolMapLoc_t.c.visitCount,
	'avgRating' : SchoolMapLoc_t.c.avgRating,
	'topicCount' : SchoolMapLoc_t.c.topicCount,
	'ranking' : SchoolMapLoc_t.c.ranking,
	'description' : SchoolMapLoc_t.c.description,
	'types' : relation(SchoolMapLocType, secondary = SchoolMapLoc_SchoolMapLocType_t, backref = 'mapLocs'),
	'pics' : relation(SchoolMapLocPic, backref = 'mapLoc'),
	'users' : relation(User, secondary = SchoolMapLoc_User_t, backref = 'schoolMapLocs'),
	'topics' : relation(SchoolMapLocTopic, backref = 'mapLoc'),
	'store' : relation(Store, uselist = False, backref = 'mapLoc'),
})

mapper(SchoolMapLocTopic, SchoolMapLocTopic_t, properties = {
	'id' : SchoolMapLocTopic_t.c.id,
	'user_id' : SchoolMapLocTopic_t.c.user_id,
	'schoolMapLoc_id' : SchoolMapLocTopic_t.c.schoolMapLoc_id,
	'createTime' : SchoolMapLocTopic_t.c.createTime,
	'updateTime' : SchoolMapLocTopic_t.c.updateTime,
	'title' : SchoolMapLocTopic_t.c.title,
	'content' : SchoolMapLocTopic_t.c.content,
	'postCount' : SchoolMapLocTopic_t.c.postCount,
	'posts' : relation(SchoolMapLocTopicPost, backref = 'topic'),
})

mapper(SchoolMapLocTopicPost, SchoolMapLocTopicPost_t, properties = {
	'id' : SchoolMapLocTopicPost_t.c.id,
	'user_id' : SchoolMapLocTopicPost_t.c.user_id,
	'createTime' : SchoolMapLocTopicPost_t.c.createTime,
	'updateTime' : SchoolMapLocTopicPost_t.c.updateTime,
	'content' : SchoolMapLocTopicPost_t.c.content,
	'topic_id' : SchoolMapLocTopicPost_t.c.topic_id,
})

mapper(SchoolMapLocPic, SchoolMapLocPic_t, properties = {
	'id' : SchoolMapLocPic_t.c.id,
	'schoolMapLoc_id' : SchoolMapLocPic_t.c.schoolMapLoc_id,
	'picCode' : SchoolMapLocPic_t.c.picCode,
	'createTime' : SchoolMapLocPic_t.c.createTime,
	'smallPic' : SchoolMapLocPic_t.c.smallPic,
	'largePic' : SchoolMapLocPic_t.c.largePic
})

mapper(SchoolMapLocType, SchoolMapLocType_t, properties = {
	'id' : SchoolMapLocType_t.c.id,
	'name' : SchoolMapLocType_t.c.name,
})

mapper(SchoolMapPiece, SchoolMapPiece_t, properties = {
	'id' : SchoolMapPiece_t.c.id,
	'posX' : SchoolMapPiece_t.c.posX,
	'posY' : SchoolMapPiece_t.c.posY,
	'width' : SchoolMapPiece_t.c.width,
	'height' : SchoolMapPiece_t.c.height,
	'filename' : SchoolMapPiece_t.c.filename,
	'schoolMap_id' : SchoolMapPiece_t.c.schoolMap_id,
})

mapper(SchoolType, SchoolType_t, properties = {
	'id' : SchoolType_t.c.id,
	'name' : SchoolType_t.c.name,
	'schools' : relation(School, backref = 'type'),
})

mapper(Store, Store_t, properties = {
	'id' : Store_t.c.id,
	'discounts' : relation(Discount, backref = 'store'),
})

mapper(SchoolMapDefault_SchoolMapLoc, SchoolMapDefault_SchoolMapLoc_t, properties = {
	'schoolMapDefualt_id' : SchoolMapDefault_SchoolMapLoc_t.c.schoolMapDefualt_id,
	'schoolMapLoc_id' : SchoolMapDefault_SchoolMapLoc_t.c.schoolMapLoc_id,
	'posX' : SchoolMapDefault_SchoolMapLoc_t.c.posX,
	'posY' : SchoolMapDefault_SchoolMapLoc_t.c.posY,
})

mapper(SchoolMapGoogle_SchoolMapLoc, SchoolMapGoogle_SchoolMapLoc_t, properties = {
	'schoolMapGoogle_id' : SchoolMapGoogle_SchoolMapLoc_t.c.schoolMapGoogle_id,
	'schoolMapLoc_id' : SchoolMapGoogle_SchoolMapLoc_t.c.schoolMapLoc_id,
	'posX' : SchoolMapGoogle_SchoolMapLoc_t.c.posX,
	'posY' : SchoolMapGoogle_SchoolMapLoc_t.c.posY,
})

mapper(SchoolMapLoc_SchoolMapLocType, SchoolMapLoc_SchoolMapLocType_t, properties = {
	'schoolMapLoc_id' : SchoolMapLoc_SchoolMapLocType_t.c.schoolMapLoc_id,
	'schoolMapLocType_id' : SchoolMapLoc_SchoolMapLocType_t.c.schoolMapLocType_id,
})

mapper(Discount_User, Discount_User_t, properties = {
	'discount_id' : Discount_User_t.c.discount_id,
	'user_id' : Discount_User_t.c.user_id,
	'type' : Discount_User_t.c.type,
	'createTime' : Discount_User_t.c.createTime,
})

mapper(School_User, School_User_t, properties = {
	'school_id' : School_User_t.c.school_id,
	'user_id' : School_User_t.c.user_id,
	'profession_id' : School_User_t.c.profession_id,
	'grade' : School_User_t.c.grade,
})

mapper(SchoolMapLoc_User, SchoolMapLoc_User_t, properties = {
	'schoolMapLoc_id' : SchoolMapLoc_User_t.c.schoolMapLoc_id,
	'user_id' : SchoolMapLoc_User_t.c.user_id,
	'rating' : SchoolMapLoc_User_t.c.rating,
	'visitCount' : SchoolMapLoc_User_t.c.visitCount
})

